﻿using MySqlX.XDevAPI;
using NLog;
using StackExchange.Redis;
using System.Data;
using TeachlyQBank.DbPool;
using TeachlyQBank.Models;

namespace TeachlyQBank.DbServer
{
    public class QuestionServer
    {
        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly MySqlConnectionAdapter _mysql; // 操作mysql的类
        private readonly IConnectionMultiplexer _redis;  // 操作redis的类
        private readonly Logger _logger;  //记录日志的类
        private readonly OracleConnectionAdapter _oracle; // 操作oracle的类

        // 完成依赖注入
        public QuestionServer(OracleConnectionAdapter oracle,MySqlConnectionAdapter mysql, IConnectionMultiplexer redis, IHttpContextAccessor httpContextAccessor)
        {
            _httpContextAccessor = httpContextAccessor;
            _redis = redis;
            _logger = LogManager.GetCurrentClassLogger();
            _mysql = mysql;
            _oracle = oracle;
        }
        // 功能:向数据库提交创建新试题的请求
        // 参数:输入一个Question类型的数据做为要创建的课程
        // 返回:执行成功返回数据库影响数据的条数1,失败返回-1
        public int AddQuestionServer(Question question)
        {
            _mysql.Open();
            string query = "insert into T_QUESTION(question_id,question_content,question_type,question_difficulty,kp_id,answer) " +
                "values(@question_id,@question_content,@question_type,@question_difficulty,@kp_id,@answer)";

            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                { "@question_id", Guid.NewGuid().ToString() ! },
                { "@question_content", question.question_content ! },
                { "@question_type", question.question_type !},
                { "@question_difficulty", question.question_difficulty !},
                {"@kp_id",question.kp_id.ToString() ! },
                {"@answer",question.answer ! },
            };


            int count = _mysql.ExecuteNonQuery(query, parameters);

            if (count > 0)
            {
                _logger.Info($"添加了一个新问题:{question.question_content},本次花费了{_mysql.GetLastExecutionTime()}毫秒");
                _mysql.Close();
                return count;
            }
            else
            {
                _logger.Error($"添加试题出现错误,错误的sql语句:{_mysql.GetLastExecutedSql()},错误的信息:{_mysql.GetLastError()}");
                _mysql.Close();
                return -1;
            }


        }
        // 功能：用于查询数据库里的所有试题并展示在前端页面
        // 参数:不需要任何参数
        // 返回:查询成功返回一个List<Question>包含了所有试题信息，查询失败返回null
        public List<Question> SltServer()
        {
            _mysql.Open();

            string query = "SELECT * FROM T_QUESTION LEFT JOIN T_KNOWLEDGE_POINT ON T_QUESTION.kp_id = T_KNOWLEDGE_POINT.pk_id;";
            DataSet data = _mysql.ExecuteQuery(query);
            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                _mysql.Close();
                // 查询出先错误
                _logger.Error($"查询出错,错误的sql语句:{_mysql.GetLastExecutedSql()},错误的信息:{_mysql.GetLastError()}");
                return null;
            }
            else
            {
                List<Question> resultList = new List<Question>();

                foreach (DataRow row in data.Tables[0].Rows)
                {
                    Question newQuestion = new Question();
                    newQuestion.question_id = row["question_id"].ToString();
                    newQuestion.question_content = row["question_content"].ToString();
                    newQuestion.question_type = row["question_type"].ToString();
                    newQuestion.question_difficulty = row["question_difficulty"].ToString();
                    newQuestion.kp_id = row["kp_id"].ToString();
                    newQuestion.kp_name = row["pk_name"].ToString();
                    newQuestion.answer = row["answer"].ToString();

                    resultList.Add(newQuestion);
                }
                _mysql.Close();
                return resultList;
            }
        }
        // 功能:根据一个试题名称删除试题
        // 参数:传入一个question里面包含了要删除试题的ID
        // 返回:执行成分返回数据库影响的条数1,失败返回-1
        public int DelQusetionServer(Question question)
        {
            _mysql.Open();
            string query = "DELETE FROM T_QUESTION WHERE question_content=@question_content";
            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                {"@question_content",question.question_content }
            };
            int count = _mysql.ExecuteNonQuery(query, parameters);
            if (count > 0)
            {
                _logger.Info($"删除了一个试题:{question.question_content},课程ID:{question.question_id},本次花费了{_mysql.GetLastExecutionTime()}毫秒");
                _mysql.Close();
                return count;
            }
            else
            {
                _logger.Error($"删除试题出现错误,错误的sql语句:{_mysql.GetLastExecutedSql()},错误的信息:{_mysql.GetLastError()}");
                _mysql.Close();
                return -1;
            }
        }
        //功能：用于查询出需要修改的试题信息单独展示在一个页面
        //参数：传入一个question里面包含了修改试题的名称
        //返回:执行成分返回数据库影响的条数1,失败返回-1
        public Question SltUpdServer(Question question)
        {
            _mysql.Open();
            string query = "SELECT * FROM T_QUESTION LEFT JOIN T_KNOWLEDGE_POINT ON T_QUESTION.kp_id = T_KNOWLEDGE_POINT.pk_id WHERE question_id=@question_id";
            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                {"@question_id",question.question_id !}
            };
            //string q = "SELECT * FROM QUESTION_TYPE";
            DataSet data = _mysql.ExecuteQuery(query, parameters);
            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                _mysql.Close();
                // 查询出错
                _logger.Error($"查询出错,错误的sql语句:{_mysql.GetLastExecutedSql()},错误的信息:{_mysql.GetLastError()}");
                return null;
            }
            else
            {
                Question question1 = new Question();

                foreach (DataRow row in data.Tables[0].Rows)
                {
                    question1.question_id = row["question_id"].ToString();
                    question1.question_content = row["question_content"].ToString();
                    question1.question_type = row["question_type"].ToString();
                    question1.question_difficulty = row["question_difficulty"].ToString();
                    question1.kp_id = row["kp_id"].ToString();
                    question1.kp_name = row["pk_name"].ToString();
                    question1.answer = row["answer"].ToString();

                    break;
                }
                _mysql.Close();
                return question1;
            }

        }
        //功能：用于修改的试题信息
        //参数：传入一个Question里面包含了修改试题的ID
        //返回:执行成分返回数据库影响的条数1,失败返回-1
        public int UpdServer(Question question)
        {
            _mysql.Open();
            string query = "UPDATE T_QUESTION SET question_content=@question_content,question_type=@question_type,question_difficulty=@question_difficulty,kp_id=@kp_id,answer=@answer WHERE question_id=@question_id;";
            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                {"@question_id",question.question_id !},
                { "@question_content", question.question_content !},
                { "@question_type", question.question_type ! },
                { "@question_difficulty", question.question_difficulty !},
                { "@kp_id", question.kp_id !},
                {"@answer",question.answer ! }

            };
            int count = _mysql.ExecuteNonQuery(query, parameters);

            if (count > 0)
            {
                _logger.Info($"对:{question.question_content}进行了更改,本次花费了{_mysql.GetLastExecutionTime()}毫秒");
                _mysql.Close();
                return count;
            }
            else
            {
                _logger.Error($"更改试题出现错误,错误的sql语句:{_mysql.GetLastExecutedSql()},错误的信息:{_mysql.GetLastError()}");
                _mysql.Close();
                return -1;
            }
        }
        //功能：根据课程名称查询出课程信息
        //参数：传入一个course里面包含了修改课程的名称
        //返回:执行成分返回数据库影响的条数1,失败返回-1
        public List<Question> Slt2Server(Question question)
        {
            _mysql.Open();
            string query = "SELECT * FROM T_QUESTION WHERE question_content LIKE @question_content";
            Dictionary<string, object> parameters = new Dictionary<string, object>
    {
        {"@question_content", "%" + question.question_content + "%" }
    };
            DataSet data = _mysql.ExecuteQuery(query, parameters);
            _mysql.Close();

            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                // 查询出现错误
                _logger.Error($"查询出错，错误的SQL语句：{_mysql.GetLastExecutedSql()}，错误的信息：{_mysql.GetLastError()}");
                return null;
            }

            List<Question> resultList = new List<Question>();

            foreach (DataRow row in data.Tables[0].Rows)
            {
                Question newQuestion = new Question();

                newQuestion.question_content = row["question_content"].ToString();
                newQuestion.question_type = row["question_type"].ToString();
                newQuestion.question_difficulty = row["question_difficulty"].ToString();
                //newQuestion.kp_id = row["kp_id"].ToString();
                newQuestion.answer = row["answer"].ToString();



                resultList.Add(newQuestion);
            }

            return resultList;
        }
        public List<Question> Slt3Server(Question question)
        {
            _mysql.Open();
            string query = "SELECT * FROM T_QUESTION WHERE question_type LIKE @question_type";
            Dictionary<string, object> parameters = new Dictionary<string, object>
    {
        {"@question_type", "%" + question.question_content + "%" }
    };
            DataSet data = _mysql.ExecuteQuery(query, parameters);
            _mysql.Close();

            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                // 查询出现错误
                _logger.Error($"查询出错，错误的SQL语句：{_mysql.GetLastExecutedSql()}，错误的信息：{_mysql.GetLastError()}");
                return null;
            }

            List<Question> resultList = new List<Question>();

            foreach (DataRow row in data.Tables[0].Rows)
            {
                Question newQuestion = new Question();

                newQuestion.question_content = row["question_content"].ToString();
                newQuestion.question_type = row["question_type"].ToString();
                newQuestion.question_difficulty = row["question_difficulty"].ToString();
                //newQuestion.kp_id = row["kp_id"].ToString();
                newQuestion.answer = row["answer"].ToString();



                resultList.Add(newQuestion);
            }

            return resultList;
        }
        public List<Knowledge> SltKLGServer()
        {
            _mysql.Open();

            //string query = "SELECT * FROM T_QUESTION LEFT JOIN T_KNOWLEDGE_POINT ON T_QUESTION.kp_id = T_KNOWLEDGE_POINT.pk_id;";
            string query = "SELECT * FROM T_KNOWLEDGE_POINT";
            DataSet data = _mysql.ExecuteQuery(query);
            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                _mysql.Close();
                // 查询出先错误
                _logger.Error($"查询出错,错误的sql语句:{_mysql.GetLastExecutedSql()},错误的信息:{_mysql.GetLastError()}");
                return null;
            }
            else
            {
                List<Knowledge> resultList = new List<Knowledge>();

                foreach (DataRow row in data.Tables[0].Rows)
                {
                    Knowledge newKnowledge = new Knowledge();
                    newKnowledge.pk_id = row["pk_id"].ToString();
                    newKnowledge.pk_name = row["pk_name"].ToString();


                    resultList.Add(newKnowledge);
                }
                _mysql.Close();
                return resultList;
            }

        }
        public Question Slt4Server(Question question1)
        {
            _oracle.Open();
            string query = "SELECT * FROM T_QUESTION_TYPE WHERE qt_id=:question_type";
            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                {":question_type",question1.question_type !}
            };
            DataSet data = _oracle.ExecuteQuery(query, parameters);
            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                _oracle.Close();
                // 查询出错
                _logger.Error($"查询出错,错误的sql语句:{_oracle.GetLastExecutedSql()},错误的信息:{_oracle.GetLastError()}");
                return null;
            }
            else
            {
                Question question2 = new Question();

                foreach (DataRow row in data.Tables[0].Rows)
                {
                    question2.question_id = question1.question_id;
                    question2.question_content = question1.question_content;
                    question2.question_type = row["type_name"].ToString();
                    question2.question_difficulty = question1.question_difficulty;
                    question2.kp_id = question1.kp_id;
                    question2.kp_name = question1.kp_name;
                    question2.answer = question1.answer;

                    break;
                }
                _oracle.Close();
                return question2;
            }

        }
    }
}
